package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserImpl implements UserDao {
    @Override
    public int add(UserModel userModel) {
//        1.封装sql语句
        String sql = "insert into user(username,password,phone,sex,birthday,hobby,introduce) " +
                "values('" + userModel.getUsername() + "'," +
                "'" + userModel.getPassword() + "'," +
                "'" + userModel.getPhone() + "'," +
                "'" + userModel.getSex() + "'," +
                "'" + userModel.getBirthday() + "'," +
                "'" + userModel.getHobby() + "'," +
                "'" + userModel.getIntroduce() + "')";
//        2.调用数据库的执行方法
        return JDBC.excuteUpdate(sql);

    }

    @Override
    public boolean findByUsername(String username) {
//        1.封装sql语句
        String sql = "select * from user where username='" + username + "'";
//        2调用JDBC查询
        ResultSet resultSet = JDBC.getResultSet(sql);
//        3.封装查询后的结果
        boolean isHave = false;
        try {
            while (resultSet.next()) {
                isHave = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        4.返回结果集
        return isHave;
    }

    @Override
    public int findByUsernameAndPassword(UserModel userModel) {
//        1.封装sql语句
//        2调用JDBC查询
//        3.封装查询后的结果
//        4.返回结果集
        String sql = "select * from user where username='" + userModel.getUsername() + "' " +
                "and password='" + userModel.getPassword() + "'";
        ResultSet resultSet = JDBC.getResultSet(sql);
        int resutl = 0;
        try {
            while (resultSet.next()) {
                resutl = 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resutl;
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        //        1.封装sql语句
        String sql = "select * from user where 1=1 ";
        if (!userModel.getUsername().equals("")) {
            sql += " and username like '%" + userModel.getUsername() + "%'";
        }
        // username like '*'
        if (!userModel.getPhone().equals("")) {
            sql += " and phone like '%" + userModel.getPhone() + "%'";
        }

        if (!userModel.getSex().equals("全部")) {
            sql += " and sex ='" + userModel.getSex() + "'";
        }
//        find_in_set
        if (!userModel.getHobby().equals("")) {
            String[] hobby = userModel.getHobby().split(",");
            for (int i = 0; i < hobby.length; i++) {
                if (i == 0) {
                    sql += " and find_in_set('" + hobby[i] + "',hobby)";
                } else {
                    sql += " or find_in_set('" + hobby[i] + "',hobby)";
                }

            }
        }

        if (!userModel.getStartBirthday().equals("") && userModel.getEndBirthday().equals("")) {
            sql += " and birthday >='" + userModel.getStartBirthday() + "'";
        }
        if (userModel.getStartBirthday().equals("") && !userModel.getEndBirthday().equals("")) {
            sql += " and birthday <='" + userModel.getEndBirthday() + "'";
        }
        if (!userModel.getStartBirthday().equals("") && !userModel.getEndBirthday().equals("")) {
            sql += " and birthday between '" + userModel.getStartBirthday() + "' and '" + userModel.getEndBirthday() + "'";
        }
//        2调用JDBC查询
        ResultSet resultSet = JDBC.getResultSet(sql);
//        3.封装查询后的结果
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                list.add(setModel(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        4.返回结果集
        return list;
    }

    @Override
    public int del(int id) {
        String sql = "delete from user where id=" + id;
        return JDBC.excuteUpdate(sql);
    }

    @Override
    public UserModel findById(int id) {
        String sql = "select * from user where id=" + id;
        ResultSet resultSet = JDBC.getResultSet(sql);
        UserModel userModel = new UserModel();
        try {
            while (resultSet.next()) {
                userModel = setModel(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userModel;
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "update user set username='" + userModel.getUsername() + "'," +
                "password='" + userModel.getPassword() + "'" +

                " where id=" + userModel.getId();
        return JDBC.excuteUpdate(sql);
    }



    private UserModel setModel(ResultSet resultSet) {
        UserModel userModel = new UserModel();
        try {
            userModel.setId(resultSet.getInt("id"));
            userModel.setUsername(resultSet.getString("username"));
            userModel.setPhone(resultSet.getString("phone"));
            userModel.setSex(resultSet.getString("sex"));
            userModel.setBirthday(resultSet.getString("birthday"));
            userModel.setHobby(resultSet.getString("hobby"));
            userModel.setIntroduce(resultSet.getString("introduce"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userModel;
    }
}
